/**
 * 
 */
package com.xbang.attendance.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.xbang.attendance.entity.product.Product;
import com.xbang.attendance.service.ProductService;
import com.xbang.attendance.utils.FieldUtil;
import com.xbang.core.dao.support.GenericDaoSupport;
import com.xbang.core.service.impl.BaseServiceImpl;
import com.xbang.core.utils.DateUtils;

/**
 * @author dev.zhang
 * 
 */
@Service("productService")
public class ProductServiceImpl extends BaseServiceImpl implements ProductService {

	@Autowired
	private GenericDaoSupport genericDaoSupport;

	public Boolean checkProduct(String code) throws Exception {
		StringBuilder sBuilder = new StringBuilder();
		sBuilder.append("SELECT COUNT(1) FROM PRODUCT WHERE PRODUCT_CODE = ?");
		int i = genericDaoSupport.getJdbcTemplate().queryForInt(sBuilder.toString(), code);
		return i > 0;
	}

	@Override
	public Product getProductByCode(String code) throws Exception {
		
		String sqlSentence = "FROM Product WHERE productCode = :productCode";
		Map<String,Object> parameters = new HashMap<String,Object>();
		
		parameters.put("productCode", code);
		
		List lists = genericDaoSupport.searchForList(sqlSentence, parameters);
		
		return  lists == null || lists.isEmpty() ? null :(Product)lists.get(0);
	}

	@Override
	public String genericExcelFile(String webPath, String[] fields)
			throws Exception {
		String genericExcelFile = "porduct-".concat(DateUtils.format(new Date(), "yyyyMMddhhmmss")).concat(".xls");
		File file = new File(webPath +"//"+ genericExcelFile);
		List orderList = getProducts();
		if(!orderList.isEmpty()){
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("sheet1");
			sheet.setDefaultColumnWidth((short) 20);
			HSSFRow row = sheet.createRow(0);// 创建第一行
			int i =0;
			for (FieldUtil.Filed field:FieldUtil.getProductFields()) {
				HSSFCell cell = row.createCell(i); 
				cell.setCellValue(field.getName());
				i++;
			}
			
			int rowIndex = 1;
			for (Object object : orderList) {
				row = sheet.createRow(rowIndex++);
				Map m = (Map) object;
				i= 0;
				for(FieldUtil.Filed field:FieldUtil.getProductFields() ){
					HSSFCell cell = row.createCell(i); 
					cell = row.createCell(i);
					cell.setCellValue( m.get(field.getCode()) != null ? m.get(field.getCode()).toString() : "");
					i++;
				}
			}
			
			OutputStream os = new FileOutputStream(file);
			workbook.write(os);// 写入
			InputStream in = new FileInputStream(file);
			os.close();
			in.close();
		}
		return genericExcelFile;
	}
	/**
	 * 得到订单导出的列表
	 * @return
	 */
	private List getProducts(){
		StringBuilder sbBuilder =new StringBuilder(" SELECT ");
		sbBuilder.append(" pro.id AS id,")
				 .append(" ca.category_code AS categoryCode,")
				 .append(" ca.category_name AS categoryName,")
				 .append(" pro.product_code AS productCode,")
				 .append(" pro.product_name AS productName,")
				 .append(" pro.specification AS specification,")
				 .append(" pro.material AS material,")
				 .append(" pro.measure_unit AS measureUnit")
				 .append(" FROM product pro")
				 .append(" LEFT JOIN Product_category ca ")
				 .append(" ON (pro.product_category_id = ca.id)");
		logger.info(sbBuilder.toString());
		return genericDaoSupport.getJdbcTemplate().queryForList(sbBuilder.toString());
	} 
}
